package com.kang.dao.impl;

import com.kang.dao.water_purifierDao;
import com.kang.entity.water_purifier;
import com.kang.util.BaseDao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 * Created by lenovo on 2018/8/4.
 */

public class water_purifierDaoImpl implements water_purifierDao
{

    public static int MaxCount=0;//有多少数据
    public static int pages=0;   //最大行页码数
    @Override
    public List<water_purifier> findWater(String name, int page, int size) {
        Connection conn= BaseDao.getConnection();
        PreparedStatement stat=null;
        ResultSet rs=null;
        List<water_purifier> water_purifierList=new ArrayList<water_purifier>();
        String sql="SELECT * FROM product where waterName like ? and state=0 limit ?,?";
        try {
            stat=conn.prepareStatement(sql);
            if(name==null)
            name="";
            stat.setString(1,"%"+name+"%");
            stat.setInt(2,page);
            stat.setInt(3,size);
            rs=stat.executeQuery();
            while(rs.next()){
                water_purifier wp=new water_purifier();
                wp.setWpid(rs.getInt("wpid"));
                wp.setWaterName(rs.getString("waterName"));
                wp.setWpnumber(rs.getString("wpnumber"));
                wp.setMamufacturer(rs.getString("mamufacturer"));
                wp.setWarranty(rs.getString("warranty"));
                wp.setModel(rs.getString("model"));
                wp.setApproval(rs.getString("approval"));
                wp.setWaterYield(rs.getString("waterYield"));
                wp.setPrice(rs.getDouble("price"));
                wp.setState(rs.getInt("state"));
                wp.setStartUsing(rs.getInt("startUsing"));
                water_purifierList.add(wp);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            BaseDao.closeConnection(conn,stat,rs);
        }
        return water_purifierList;
    }

    @Override
    public int delWater(int id) {
        String sql="update product set state=1 where wpid=?";
        return BaseDao.executeUpdate(sql,id);
    }

    @Override
    public int addWater(water_purifier w) {
       Object ob[]={w.getWaterName(),"No."+findMaxID()+1,w.getMamufacturer(),w.getWarranty(),
       w.getModel(),w.getApproval(),w.getWaterYield(),w.getPrice()
       };
       String sql="Insert product values(null,?,?,?,?,?,?,?,?,0,0)";
       int i= BaseDao.executeUpdate(sql,ob);
        return i;
    }

    @Override
    public int findMaxID() {
        Connection conn= BaseDao.getConnection();
        PreparedStatement stat=null;
        ResultSet rs=null;
        int i=0;
        String sql="select wpid  from product where wpid=(select max(wpid) from product)";
        try {
            stat=conn.prepareStatement(sql);
            rs=stat.executeQuery();
            if(rs.next()){
            i=rs.getInt(1);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            BaseDao.closeConnection(conn,stat,rs);
        }
        return i;
    }

    @Override
    public int findMaxCount(String name) {
        Connection conn= BaseDao.getConnection();
        PreparedStatement stat=null;
        ResultSet rs=null;

        String sql="SELECT COUNT(1) From product WHERE waterName like ?  and state=0 ";
        try {
            stat=conn.prepareStatement(sql);
            stat.setString(1,"%"+name+"%");
            rs=stat.executeQuery();
            if(rs.next()){
                MaxCount=rs.getInt(1);
                pages=(MaxCount+3-1)/3;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            BaseDao.closeConnection(conn,stat,rs);
        }
        return MaxCount;
    }

    @Override
    public int updataStartUsing(int falgs,int wpids) {
        String sql="UPDATE product set startUsing=?  where wpid=?";
       Object ob[]={falgs,wpids};
        return BaseDao.executeUpdate(sql,ob);
    }

    @Override
    public water_purifier findWaterById(int wpid) {
        Connection conn= BaseDao.getConnection();
        PreparedStatement stat=null;
        ResultSet rs=null;
        water_purifier wp=new water_purifier();

        String sql="SELECT * FROM product WHERE wpid=?";
        try {
            stat=conn.prepareStatement(sql);
            stat.setInt(1,wpid);
           rs=stat.executeQuery();
            if(rs.next()){
                wp.setWpid(rs.getInt("wpid"));
                wp.setWaterName(rs.getString("waterName"));
                wp.setWpnumber(rs.getString("wpnumber"));
                wp.setMamufacturer(rs.getString("mamufacturer"));
                wp.setWarranty(rs.getString("warranty"));
                wp.setModel(rs.getString("model"));
                wp.setApproval(rs.getString("approval"));
                wp.setWaterYield(rs.getString("waterYield"));
                wp.setPrice(rs.getDouble("price"));
                wp.setState(rs.getInt("state"));
                wp.setStartUsing(rs.getInt("startUsing"));
            }
            rs=stat.executeQuery();
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            BaseDao.closeConnection(conn,stat,rs);
        }
        return wp;
    }

    @Override
    public int updateWaterById( water_purifier w) {
        Object ob[]={w.getWaterName(),w.getMamufacturer(),w.getWarranty(),
                w.getModel(),w.getApproval(),w.getWaterYield(),w.getPrice(),w.getWpid()
        };
        String sql="UPDATE product set WaterName=?,Mamufacturer=?,Warranty=? ,Model=? ,Approval=? ,WaterYield=?,Price=? where wpid=?";
        int i= BaseDao.executeUpdate(sql,ob);
        return i;
    }
}
